#!/bin/bash
cd /home/eve
rm -f /home/eve/importme-temp.dump
DATETIME="`date -d'1 day ago' --rfc-3339=date`"
wget -N http://eve-central.com/dumps/$DATETIME.dump.gz &> /dev/null
if ls /home/eve/$DATETIME.dump.gz &> /dev/null
then
gunzip /home/eve/$DATETIME.dump.gz
#Wipe out expired orders in original data first
echo "DELETE FROM transactions WHERE duration = '365:00:00:00.00' AND DATE_ADD( issued, INTERVAL 366 DAY ) <= CURDATE( );" |/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '90:00:00:00.00' AND DATE_ADD( issued, INTERVAL 91 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '30:00:00:00.00' AND DATE_ADD( issued, INTERVAL 31 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '14:00:00:00.00' AND DATE_ADD( issued, INTERVAL 15 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '7:00:00:00.00' AND DATE_ADD( issued, INTERVAL 8 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '3:00:00:00.00' AND DATE_ADD( issued, INTERVAL 4 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '1:00:00:00.00' AND DATE_ADD( issued, INTERVAL 2 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
ps -ef | grep dupes-olddata.php | grep -v 'grep' | awk '{print$2}' | xargs -i kill -9 {}
echo "INSERT INTO transactions SELECT * FROM newtransactionsstage;" |mysql --user=root --password=password eve_dbo
sleep 10
echo "source /home/eve/temptable" |mysql --user=root --password=password eve_dbo
echo "DROP TABLE IF EXISTS newtransactionsstage;" |mysql --user=root --password=password eve_dbo
echo "CREATE TABLE newtransactionsstage SELECT * FROM transactions;" |mysql --user=root --password=password eve_dbo
cat /home/eve/$DATETIME.dump |sed -e "s/ , /,/g" -e "s/ ,/,/g" | grep -v orderid > /home/eve/importme-temp.dump
#Create newtransactionstmp with new raw data
echo "LOAD DATA LOCAL INFILE '/home/eve/importme-temp.dump' INTO TABLE newtransactionstmp FIELDS TERMINATED BY ',' (orderid, regionid, systemid, stationid, typeid, bid, price, minvolume, volremain, volenter, issued, duration, range, reportedby, reportedtime);"|mysql --user=root --password=password eve_dbo
#Create unique list of item per region from raw data.  These will be deleted from old data prior to insertion of new raw data.
echo "insert into newregionitems (regionid, typeid) SELECT DISTINCT regionid, typeid FROM newtransactionstmp;"|mysql --user=root --password=password eve_dbo
mv /home/eve/$DATETIME.dump /home/eve/bak/
#kill all existing itemid's per region before inserting new data
php -f /var/www/html/kiroshi/dupes.php
#Create a list of unique orderID per regionid for use to keep only newest
#echo "insert into newregionorderid (regionid, orderid) SELECT DISTINCT regionid, orderid FROM newtransactionstmp;"|mysql --user=root --password=password eve_dbo
#insert cleaned up new data into newtransactions (needs to be temp table)
#the following is cleaned up new raw data to replace the impotme-temp.dum import
#php -f /var/www/html/kiroshi/dupes-newdata.php
#newtransactionsstage from previous to replace the following:
sync
sleep 10
echo "LOAD DATA LOCAL INFILE '/home/eve/importme-temp.dump' INTO TABLE newtransactionsstage FIELDS TERMINATED BY ',' (orderid, regionid, systemid, stationid, typeid, bid, price, minvolume, volremain, volenter, issued, duration, range, reportedby, reportedtime);"|mysql --user=root --password=password eve_dbo
#Drop original table 'transactions' and rename 'newtransactionsstage' as 'transactions'
#echo "RENAME TABELE transactions TO transactionsbak;" |mysql --user=root --password=password eve_dbo
sync
sleep 60
echo "DROP TABLE IF EXISTS transactions;" |mysql --user=root --password=password eve_dbo
sync
sleep 10
echo "RENAME TABLE newtransactionsstage TO transactions;" |mysql --user=root --password=password eve_dbo
sync
sleep 60
#Start deleting duplicate orderids per region
#Tash-Murkon region first
echo "source /home/eve/temptable" |mysql --user=root --password=password eve_dbo
echo "INSERT INTO newregionorderid SELECT count(*) AS orders, regionid, orderid FROM transactions WHERE regionid = '10000020' GROUP BY regionid, orderid HAVING orders > 1 ORDER BY count(*) DESC;"|mysql --user=root --password=password eve_dbo
php -f /var/www/html/kiroshi/dupes-olddata.php
sleep 60
#Ipref region
#echo "source /home/eve/temptable" |mysql --user=root --password=password eve_dbo
#echo "INSERT INTO newregionorderid SELECT count(*) AS orders, regionid, orderid FROM transactions WHERE regionid = '10000049' GROUP BY regionid, orderid HAVING orders > 1 ORDER BY count(*) DESC;"|mysql --user=root --password=password eve_dbo
#php -f /var/www/html/kiroshi/dupes-olddata.php
#Jita region
#echo "source /home/eve/temptable" |mysql --user=root --password=password eve_dbo
#echo "INSERT INTO newregionorderid SELECT count(*) AS orders, regionid, orderid FROM transactions WHERE regionid = '10000002' GROUP BY regionid, orderid HAVING orders > 1 ORDER BY count(*) DESC;"|mysql --user=root --password=password eve_dbo
#php -f /var/www/html/kiroshi/dupes-olddata.php
#sleep 60
#Now do the other regions
echo "source /home/eve/temptable" |mysql --user=root --password=password eve_dbo
echo "INSERT INTO newregionorderid SELECT count(*) AS orders, regionid, orderid FROM transactions GROUP BY regionid, orderid HAVING orders > 1 ORDER BY count(*) DESC;"|mysql --user=root --password=password eve_dbo
sync
sleep 60
php -f /var/www/html/kiroshi/dupes-olddata.php
else
echo "Failed downloading - cleaning up expired orders"
echo "DELETE FROM transactions WHERE duration = '365:00:00:00.00' AND DATE_ADD( issued, INTERVAL 366 DAY ) <= CURDATE( );" |/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '90:00:00:00.00' AND DATE_ADD( issued, INTERVAL 91 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '30:00:00:00.00' AND DATE_ADD( issued, INTERVAL 31 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '14:00:00:00.00' AND DATE_ADD( issued, INTERVAL 15 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '7:00:00:00.00' AND DATE_ADD( issued, INTERVAL 8 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '3:00:00:00.00' AND DATE_ADD( issued, INTERVAL 4 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
echo "DELETE FROM transactions WHERE duration = '1:00:00:00.00' AND DATE_ADD( issued, INTERVAL 2 DAY ) <= CURDATE( );"|/usr/bin/mysql --user=root --password=password eve_dbo
fi
